/*
 * Copyright (C), 2002-2017, 苏宁易购电子商务有限公司
 * FileName: MainPushHqImportServiceImpl.java
 * Author:   15050536
 * Date:     2017年1月9日 下午3:55:19
 * Description: //模块目的、功能描述      
 * History: //修改记录
 * <author>      <time>      <version>    <desc>
 * 修改人姓名             修改时间            版本号                  描述
 */
package com.suning.sawp.service.impl.mainpushnew;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.base.Function;
import com.google.common.base.Joiner;
import com.google.common.collect.ImmutableListMultimap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Multimaps;
import com.suning.sawp.constants.ConfigConstants;
import com.suning.sawp.constants.MainPushConstants;
import com.suning.sawp.constants.MainPushErrorConstants;
import com.suning.sawp.dto.common.ReturnMsg;
import com.suning.sawp.dto.maindiscode.MainDiscodeDto;
import com.suning.sawp.exception.SawpException;
import com.suning.sawp.intf.dao.mainpushnew.MainPushTaskHqHandleDao;
import com.suning.sawp.intf.mainpushnew.MainPushHqImportService;
import com.suning.sawp.po.mainpushnew.AreaModel;
import com.suning.sawp.po.mainpushnew.AreaTask;
import com.suning.sawp.po.mainpushnew.HqTask;
import com.suning.sawp.po.mainpushnew.Model;
import com.suning.sawp.po.mainpushnew.ModelCmmdty;
import com.suning.sawp.po.mainpushnew.ModelCmmdtyVo;
import com.suning.sawp.po.store.StoreBaseInfo;
import com.suning.sawp.service.util.BeanUtil;
import com.suning.sawp.service.util.MainPushExcelHandle;
import com.suning.sawp.service.util.RedisCacheUtils;

/**
 * 总部主推任务，导出excel功能
 *
 * @author 15050536 石键平
 */
@Service("mainPushHqImportService")
public class MainPushHqImportServiceImpl implements MainPushHqImportService {

	private Logger LOGGER = LoggerFactory.getLogger(MainPushHqImportServiceImpl.class);

	@Autowired
	private MainPushTaskHqHandleDao mainPushTaskHqHandleDao;

	@Resource
	private RedisCacheUtils cacheUtils;

	@Override
	@Transactional
	public ReturnMsg<List<String>> importFirstExcel(MultipartFile file, String month) {
		MainPushExcelHandle excelHandle = new MainPushExcelHandle(file);
		// 获取所有的单元格
		List<Sheet> sheetList = excelHandle.getSheetsFromWorkbook();

		// 所有品类名称的列表，用来判断是否有重复的品类
		List<String> allCateNames = new ArrayList<String>();
		// 解析占比数据，并保存
		List<String> errorList = parseRateSheet(excelHandle, sheetList, month, allCateNames);
		// 占比数据解析正确后，解析 款数与商品关系的数据
		if (CollectionUtils.isEmpty(errorList)) {
			errorList = parseModelSheet(excelHandle, sheetList, month, allCateNames);
		}

		ReturnMsg<List<String>> returnMsg = new ReturnMsg<List<String>>();
		if (!CollectionUtils.isEmpty(errorList)) {
			returnMsg.setData(errorList);
			returnMsg.setRetFlag(ReturnMsg.FAIL);
		}
		return returnMsg;
	}

	/**
	 * 解析占比的sheet
	 *
	 * @return
	 */
	private List<String> parseRateSheet(MainPushExcelHandle excelHandle, List<Sheet> sheetList, String month, List<String> allCateNames) {
		// 获得占比数据
		List<List<String>> rateList = excelHandle.getExcelContentFromSheet(sheetList.get(1), 1, -1);
		LOGGER.debug("获得的占比数据为：{}", rateList);
		// 解析占比数据，生成总部主推任务 并保存
		List<String> errorList = saveHqTask(rateList, month, allCateNames);
		return errorList;
	}

	/**
	 * 保存总部任务数据
	 *
	 */
	@SuppressWarnings("unchecked")
	private List<String> saveHqTask(List<List<String>> rateList, String month, List<String> allCateNames) {
		List<String> errorList = new ArrayList<String>();

		// 1.校验是否是空excel(占比数据为空)
		if (CollectionUtils.isEmpty(rateList)) {
			// 占比数据不可以为空
			errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_003));
			return errorList;
		}
		// 2.校验品类数量(超过8大品类了)
		if (rateList.size() > 8) {
			// 占比数据不可以为空
			errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_002));
		}

		// 根据excel内容创建任务list
		List<HqTask> taskList = new ArrayList<HqTask>();

		parseRateList(rateList, allCateNames, taskList, errorList, month);

		if (hasSame(allCateNames)) {
			errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_002));
		}

		// 没有错误，并且解析到数据
		if (CollectionUtils.isEmpty(errorList) && !CollectionUtils.isEmpty(taskList)) {

			Map<String, Object>[] batchValues = new Map[taskList.size()];
			for (int i = 0; i < taskList.size(); i++) {
				HqTask hqTask = taskList.get(i);
				batchValues[i] = BeanUtil.describe(hqTask);
			}
			mainPushTaskHqHandleDao.batchSaveHqTask(batchValues);
		}
		return errorList;
	}

	/**
	 * 把得到的占比list 转换成hqtask对象，并入库
	 */
	private void parseRateList(List<List<String>> rateList, List<String> allCateNames, List<HqTask> taskList, List<String> errorList, String month) {
		// 占比数据不可以重复
		int i = 0;
		String rowNum = null;
		List<HqTask> hqTasks = mainPushTaskHqHandleDao.queryHqTaskByMonth(month);
		try {
			for (List<String> columns : rateList) {
				rowNum = "第" + (i + 2) + "行 ";

				if (columns.size() <= 1) { // 基本不会走到此分支
					String errorMsg = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_005), rowNum);
					errorList.add(errorMsg);
					continue;
				}
				// 品类名称
				String cateName = columns.get(0);
				// 占比
				String rate = columns.get(1);
				allCateNames.add(cateName);
				// 根据品类名称获取品类编码
				String cateCode = getCateCodeByName(cateName);
				if (StringUtils.isBlank(cateCode)) {
					errorList.add(rowNum + getErrorMsg(MainPushErrorConstants.TASK_ERR_001) + ":" + cateName);
				}
				// 判断某品类在所选择月份是否已创建过任务了
				if (checkRepeat(cateCode, hqTasks)) {
					errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_006), cateName));
				}

				BigDecimal _rate = new BigDecimal(rate);
				if (_rate.doubleValue() < 0.0d || _rate.doubleValue() > 1d) {
					String errorMsg = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_005), rowNum);
					errorList.add(errorMsg);
				}
				HqTask hqTask = new HqTask(month, cateCode, cateName, _rate);
				taskList.add(hqTask);
				i++;
			}
		} catch (NumberFormatException e) {
			LOGGER.error("占比要求不是数字类型!", e);
			String errorMsg = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_005), rowNum);
			errorList.add(errorMsg);
		}
	}

	/**
	 * 判断list里面是否有重复数据
	 */
	private static boolean hasSame(List<? extends Object> list) {
		if (null == list)
			return false;
		return list.size() != new HashSet<Object>(list).size();
	}

	private String getErrorMsg(String errorCode) {
		return MainPushErrorConstants.MAIN_PUSH_ERR_MAP.get(errorCode);
	}

	private String getCateCodeByName(String cateName) {
		if (cateName != null && ConfigConstants.DEFAULT_CATE_MAP.containsValue(cateName)) {
			for (Entry<String, String> entry : ConfigConstants.DEFAULT_CATE_MAP.entrySet()) {
				if (entry.getValue().equals(cateName)) {
					return entry.getKey();
				}
			}
		}
		return null;
	}

	/**
	 * 判断某品类是否已经创建过主推任务了
	 *
	 * @param cateCode
	 * @return
	 */
	private boolean checkRepeat(String cateCode, List<HqTask> hqTasksInDB) {
		if (CollectionUtils.isEmpty(hqTasksInDB)) {
			return false;
		}
		for (HqTask hqTask : hqTasksInDB) {
			if (hqTask.getCateCode().equals(cateCode)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 解析款数与商品的关系
	 */
	private List<String> parseModelSheet(MainPushExcelHandle excelHandle, List<Sheet> sheetList, String month, List<String> allCateNames) {
		// 获得占比数据
		List<List<String>> modelCmmdtyList = excelHandle.getExcelContentFromSheet(sheetList.get(0), 1, -1);
		LOGGER.debug("获得的款数商品数据为：{}", modelCmmdtyList);
		// 解析占比数据，生成总部主推任务 并保存
		List<String> errorList = parseModelCmmdtyList(modelCmmdtyList, month, allCateNames);
		return errorList;
	}

	/**
	 * 解析通过sheet得到的list 款数与商品关系
	 */
	private List<String> parseModelCmmdtyList(List<List<String>> modelCmmdtyList, String month, List<String> allCateNames) {
		List<String> errorList = new ArrayList<String>();
		// 1.校验是否是空excel(款数数据为空)
		if (CollectionUtils.isEmpty(modelCmmdtyList)) {
			// 款数与商品数据不可以为空
			errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_101));
			return errorList;
		}
		// 所有解析后的数据，都会入此map
		Map<String, List<ModelCmmdtyVo>> allModelCmmdtysMap = new LinkedHashMap<String, List<ModelCmmdtyVo>>();
		// 品类set，最多仅允许8个
		Set<String> cateSet = new HashSet<String>();
		// 商品编码list
		List<String> cmmdtyCodeList = new ArrayList<String>();
		String rowNum = null;
		int i = 0;
		for (List<String> columns : modelCmmdtyList) {
			rowNum = "第" + (i + 2) + "行 ";
			i++;
			if (columns.size() <= 5) { // 基本不会走到此分支
				String errorMsg = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_102), rowNum);
				errorList.add(errorMsg);
				return errorList;
			}
			// 品类编码
			String cateName = columns.get(0);
			// 款数名称
			String modelName = columns.get(1);
			// 品牌
			String brand = columns.get(2);
			// 商品编码
			String cmmdtyCode = columns.get(3);
			// 型号
			String xh = columns.get(4);
			// 主推类型 A，B
			String mpType = columns.get(5);
			// 根据品类名称获取品类编码
			String cateCode = getCateCodeByName(cateName);
			if (StringUtils.isBlank(cateCode)) {
				errorList.add(rowNum + getErrorMsg(MainPushErrorConstants.TASK_ERR_001) + ":" + cateName);
			}
			if (StringUtils.isBlank(modelName)) {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_009), rowNum));
			}
			if (StringUtils.isBlank(cmmdtyCode)) {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_010), rowNum));
			}
			if (StringUtils.isBlank(brand)) {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_012), rowNum));
			}
			if (cmmdtyCodeList.contains(cmmdtyCode)) {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_103), rowNum, cmmdtyCode));
			} else {
				cmmdtyCodeList.add(cmmdtyCode);
			}

			if (StringUtils.isBlank(mpType)) {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_011), rowNum));
			}

			if (!cateSet.contains(cateName)) {
				cateSet.add(cateName);
			}

			ModelCmmdtyVo vo = new ModelCmmdtyVo(cateCode, cateName, modelName, brand, cmmdtyCode, xh, mpType);

			if (allModelCmmdtysMap.containsKey(cateCode)) {
				allModelCmmdtysMap.get(cateCode).add(vo);
			} else {
				List<ModelCmmdtyVo> modelCmmdtys = new LinkedList<ModelCmmdtyVo>();
				modelCmmdtys.add(vo);
				allModelCmmdtysMap.put(cateCode, modelCmmdtys);
			}
		}
		// 验证占比的品类 和 AB款数明细sheet里的品类是否一一对应
		checkCateOneToOne(allCateNames, cateSet, errorList);

		checkMpType(allModelCmmdtysMap, errorList);
		// 没有错误，那么数据可以入库了
		if (CollectionUtils.isEmpty(errorList)) {
			saveModelAndCmmdty(allModelCmmdtysMap, month);
			// 保存成功之后，记录一个key，表示第一步成功，可以去处理第二步了,半小时内，若页面未跳转走，可以继续传
			cacheUtils.setex(MainPushConstants.HQ_TASK_PARSE_FIRST_XLS_SUCCESS, 1800, "1");
		}

		return errorList;
	}

	private void checkCateOneToOne(List<String> allCateNames, Set<String> cateSet, List<String> errorList) {
		Map<String, Integer> map = new HashMap<String, Integer>();
		if (allCateNames.size() != cateSet.size()) {
			String errorMsg = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_008));
			errorList.add(errorMsg);
			return;
		} else {
			for (String key : allCateNames) {
				map.put(key, 1);
			}
			for (String key : cateSet) {
				if (map.containsKey(key)) {
					map.put(key, 2);
				}
			}
			for (Map.Entry<String, Integer> entry : map.entrySet()) {
				if (entry.getValue() != 2) {
					String errorMsg = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_008));
					errorList.add(errorMsg);
					return;
				}
			}
		}

	}

	/**
	 * 
	 * 检测主推类别，同一款数下，主推类别必须一一对应
	 *
	 * @param allModelCmmdtysMap
	 * @return
	 */
	private void checkMpType(Map<String, List<ModelCmmdtyVo>> allModelCmmdtysMap, List<String> errorList) {
		int i = 0;
		String rowNum = null;
		for (Map.Entry<String,List<ModelCmmdtyVo>> cateData : allModelCmmdtysMap.entrySet()) {
			List<ModelCmmdtyVo> modelCmmdtyVoList = cateData.getValue();
			// 按款数名称分组
			Map<String, Collection<ModelCmmdtyVo>> map = groupModelCmmdty(modelCmmdtyVoList);
			for (Map.Entry<String,Collection<ModelCmmdtyVo>> modelData : map.entrySet()) {
				rowNum = "第" + (i + 2) + "行 ";

				// 某一款数对应的数据
				Collection<ModelCmmdtyVo> modelCmmdtys = modelData.getValue();
				if (modelCmmdtys.size() <= 1) {
					i++;
					continue;
				}
				Set<String> set = new HashSet<String>();
				int j = 0;
				for (ModelCmmdtyVo modelCmmdtyVo : modelCmmdtys) {
					if (!set.contains(modelCmmdtyVo.getMpType())) {
						set.add(modelCmmdtyVo.getMpType());
					}
					if (set.size() > 1) {
						String errorMsg = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_104), rowNum, modelData.getKey());
						errorList.add(errorMsg);
						i += (modelCmmdtys.size() - j);
						break;
					}
					j++;
					i++;
				}
			}
		}
	}

	/**
	 * 
	 * 保存款数与款数商品关系
	 *
	 * @param allModelCmmdtysMap
	 * @param month
	 */
	@SuppressWarnings("unchecked")
	private void saveModelAndCmmdty(Map<String, List<ModelCmmdtyVo>> allModelCmmdtysMap, String month) {
		List<HqTask> hqTaskList = mainPushTaskHqHandleDao.queryHqTaskByMonth(month);
		for (Map.Entry<String,List<ModelCmmdtyVo>> cateData : allModelCmmdtysMap.entrySet()) {
			Long hqTaskId = getHqTaskId(hqTaskList, cateData.getKey());
			// 当前品类无任务id。出现此情况为：占比导入五个品类，关系里面超过5个品类了
			if (hqTaskId == null) {
				throw new SawpException(String.format("%s，品类%s，无总部任务,请确保数据正确", month, cateData.getKey()));
			}
			// 按款数名称分组
			Map<String, Collection<ModelCmmdtyVo>> map = groupModelCmmdty(cateData.getValue());
			for (Map.Entry<String,Collection<ModelCmmdtyVo>> modelData : map.entrySet()) {
				// 某一款数对应的数据
				Collection<ModelCmmdtyVo> modelCmmdtys = modelData.getValue();
				Model model = null;
				// 款数入库
				for (ModelCmmdtyVo modelCmmdtyVo : modelCmmdtys) {
					model = new Model(cateData.getKey(), modelCmmdtyVo.getCateName(), modelData.getKey(), modelCmmdtyVo.getMpType());
					model.setHqTaskId(hqTaskId);
					mainPushTaskHqHandleDao.insertModel(model);
					break;
				}
				if (model == null) {
					continue;
				}

				// 款数与商品关系入库
				Map<String, Object>[] params = new Map[modelCmmdtys.size()];
				int i = 0;
				for (ModelCmmdtyVo modelCmmdtyVo : modelCmmdtys) {
					ModelCmmdty modelCmmdty = new ModelCmmdty(modelData.getKey(), modelCmmdtyVo.getCmmdtyCode(), cateData.getKey(), modelCmmdtyVo.getBrand(), modelCmmdtyVo.getXh());
					modelCmmdty.setHqTaskId(hqTaskId);
					modelCmmdty.setModelId(model.getId());
					params[i] = BeanUtil.describe(modelCmmdty);
					i++;
				}
				mainPushTaskHqHandleDao.batchSaveModelCmmdtyRel(params);
			}
		}
	}

	/**
	 * 
	 * 根据品类编码，获取总部任务id
	 *
	 * @param hqTaskList
	 *            参数月份对应的所有总部任务
	 * @param cateCode
	 *            品类id
	 * @return
	 */
	private Long getHqTaskId(List<HqTask> hqTaskList, String cateCode) {
		for (HqTask hqTask : hqTaskList) {
			if (hqTask.getCateCode().equals(cateCode)) {
				return hqTask.getId();
			}
		}
		return null;
	}

	/**
	 * 
	 * 根据款数名称，分组
	 *
	 * @param modelCmmdtys
	 * @return
	 */
	private Map<String, Collection<ModelCmmdtyVo>> groupModelCmmdty(List<ModelCmmdtyVo> modelCmmdtys) {
		Function<ModelCmmdtyVo, String> codeFunction = new Function<ModelCmmdtyVo, String>() {
			@Override
			public String apply(ModelCmmdtyVo mc) {
				return mc.getModelName();
			}
		};
		ImmutableListMultimap<String, ModelCmmdtyVo> map = Multimaps.index(modelCmmdtys, codeFunction);
		return map.asMap();
	}

	// 导入第二excel并解析
	@Transactional
	@Override
	public ReturnMsg<List<String>> importSecondExcel(MultipartFile file, String month) {
		// 获取总部任务列表
		List<HqTask> hqTasks = mainPushTaskHqHandleDao.queryHqTaskByMonth(month);
		ReturnMsg<List<String>> returnMsg = new ReturnMsg<List<String>>();
		if (CollectionUtils.isEmpty(hqTasks)) {
			returnMsg.setError(MainPushErrorConstants.TASK_ERR_100, MainPushErrorConstants.MAIN_PUSH_ERR_MAP);
			return returnMsg;
		}
		// 解析excel
		MainPushExcelHandle excelHandle = new MainPushExcelHandle(file);
		// 获取所有的单元格
		List<Sheet> sheetList = excelHandle.getSheetsFromWorkbook();
		// 解析所有的sheet
		List<String> errorList = parseAllSheets(sheetList, hqTasks, excelHandle, month);

		if (!CollectionUtils.isEmpty(errorList)) {
			returnMsg.setData(errorList);
			returnMsg.setRetFlag(ReturnMsg.FAIL);
		}
		return returnMsg;
	}

	private List<String> parseAllSheets(List<Sheet> sheetList, List<HqTask> hqTasks, MainPushExcelHandle excelHandle, String month) {

		List<String> errorList = new ArrayList<String>();

		parseSheets(sheetList, errorList, excelHandle, hqTasks, month);

		return errorList;
	}

	/**
	 * 验证sheet是否正确
	 */
	private void checkSheet(List<Sheet> sheetList, List<HqTask> hqTasks, List<String> errorList) {
		if (CollectionUtils.isEmpty(sheetList) || sheetList.size() > 8) {
			errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_002));
		}
		for (Sheet sheet : sheetList) {
			String cateName = sheet.getSheetName();
			String cateCode = getCateCodeByName(cateName);
			if (StringUtils.isBlank(cateCode)) {
				errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_001) + ":" + cateName);
			}
			// 判断某品类在所选择月份是否已创建过任务了,如果没创建，则报错
			if (!checkRepeat(cateCode, hqTasks)) {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_007), cateName));
			}
		}
	}

	/**
	 * 具体解析一个个sheet
	 *
	 * @param sheetList
	 * @param errorList
	 */
	private void parseSheets(List<Sheet> sheetList, List<String> errorList, MainPushExcelHandle excelHandle, List<HqTask> hqTasks, String month) {
		// 检测sheet是否合规
		checkSheet(sheetList, hqTasks, errorList);

		// 查询所有大区
		List<MainDiscodeDto> areaList = mainPushTaskHqHandleDao.queryAllArea();
		if (CollectionUtils.isEmpty(areaList)) {
			errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_105));
			return;
		}
		// 查询所选月份 所有的款数信息
		List<Model> modelList = mainPushTaskHqHandleDao.queryAllModelByMonth(month);
		if (CollectionUtils.isEmpty(modelList)) {
			errorList.add(getErrorMsg(MainPushErrorConstants.TASK_ERR_101));
			return;
		}
		Map<String, Collection<Model>> modelMap = groupModel(modelList);

		for (int i = 0; i < sheetList.size(); i++) {
			Sheet sheet = sheetList.get(i);
			String cateName = sheet.getSheetName();
			String cateCode = getCateCodeByName(cateName);
			if (StringUtils.isBlank(cateCode)) {
				continue;
			}
			// 库中对应的款数信息
			Collection<Model> modelsInDB = modelMap.get(cateCode);
			// 获取sheet里的数据
			List<List<String>> totalList = excelHandle.getExcelContentFromSheet(sheet, 0, -1);

			// -1是 减掉 title占据的那行
			if (totalList.size() - 1 != areaList.size()) {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_106), cateName, areaList.size(), (totalList.size() - 1)));
			}

			if (totalList.size() > 1) {
				// 款数信息
				List<String> modelsList = totalList.get(0);
				// 对款数做校验
				if (modelsList.size() <= 1) {
					errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_112), cateName));
					continue;
				}
				String diffModel = getDiffModels(modelsInDB, modelsList);
				if (StringUtils.isNotBlank(diffModel)) {
					errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_109), cateName, diffModel));
				}
				// 对大区数据做校验
				List<List<String>> datas = totalList.subList(1, totalList.size());
				String diffArea = getDiffAreas(areaList, datas);
				if (StringUtils.isNotBlank(diffArea)) {
					errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_107), cateName, diffArea));
				}
				// 对数据信息做校验
				checkData(datas, cateName, errorList);
				// 没有错误，则保存数据
				if (CollectionUtils.isEmpty(errorList)) {
					HqTask hqTask = getHqTaskByCateCode(hqTasks, cateCode);
					if (hqTask == null) { // 基本不会出现此情况，上面的代码早判断过了
						throw new SawpException("总部任务获取失败:" + cateCode);
					}
					// 保存数据入库
					saveSecondFileData(totalList, hqTask, modelsInDB, areaList);
					// 数据保存成功之后，删除缓存数据
					cacheUtils.del(MainPushConstants.HQ_TASK_PARSE_FIRST_XLS_SUCCESS);
				}

			} else {
				errorList.add(String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_108), cateName));
			}
		}
	}

	private HqTask getHqTaskByCateCode(List<HqTask> hqTasks, String cateCode) {
		for (HqTask hqTask : hqTasks) {
			if (hqTask.getCateCode().equals(cateCode)) {
				return hqTask;
			}
		}
		return null;
	}

	private boolean containsArea(List<String> areaNameList, String areaName) {
		for (Iterator<String> iter = areaNameList.iterator(); iter.hasNext();) {
			if (iter.next().indexOf(areaName) > -1) {
				iter.remove();
				return true;
			}
		}
		return false;
	}

	/**
	 * 比较库中大区，和文件大区，返回不同的数据
	 *
	 * @return
	 */
	private String getDiffAreas(List<MainDiscodeDto> areasInDB, List<List<String>> areasInFile) {
		List<String> listDB = Lists.newArrayList();
		List<String> listFile = Lists.newArrayList();

		for (Iterator<MainDiscodeDto> iterDB = areasInDB.iterator(); iterDB.hasNext();) {
			MainDiscodeDto mainDiscodeDto = iterDB.next();
			listDB.add(mainDiscodeDto.getNote());
		}

		for (Iterator<List<String>> iterFile = areasInFile.iterator(); iterFile.hasNext();) {
			List<String> columns = iterFile.next();
			String areaName = columns.get(0);
			// 不包含
			if (!containsArea(listDB, areaName)) {
				listFile.add(areaName);
			}
		}

		String result = StringUtils.EMPTY;
		if (CollectionUtils.isNotEmpty(listDB)) {
			result = "库中有文件无的大区：" + Joiner.on(",").join(listDB) + "\n";
		}

		if (CollectionUtils.isNotEmpty(listFile)) {
			result += "库中无文件有的大区：" + Joiner.on(",").join(listFile);
		}

		return result;
	}

	/**
	 * 比较库中款数，和文件中款数，返回不同的数据
	 *
	 * @return
	 */
	private String getDiffModels(Collection<Model> modelsInDB, List<String> modelsInFile) {
		List<String> listDB = Lists.newArrayList();
		List<String> listFile = Lists.newArrayList();
		for (Iterator<Model> iterDB = modelsInDB.iterator(); iterDB.hasNext();) {
			Model model = iterDB.next();
			if (!MainPushConstants.PUSH_TYPE_B2.equals(model.getTaskType())) {
				listDB.add(model.getModelName());
			}
		}

		for (int i = 1; i < modelsInFile.size(); i++) {
			String modelName = modelsInFile.get(i);
			// 如果包含，表名相同
			if (listDB.contains(modelName)) {
				listDB.remove(modelName);
			} else {
				listFile.add(modelName);
			}
		}

		String result = StringUtils.EMPTY;
		if (CollectionUtils.isNotEmpty(listDB)) {
			result = "库中有文件无的款数：" + Joiner.on(",").join(listDB) + "\n";
		}
		if (CollectionUtils.isNotEmpty(listFile)) {
			result += "库中无文件有的款数：" + Joiner.on(",").join(listFile);
		}

		return result;
	}

	private Map<String, Collection<Model>> groupModel(List<Model> modelList) {
		Function<Model, String> function = new Function<Model, String>() {
			@Override
			public String apply(Model model) {
				return model.getCateCode();
			}
		};
		ImmutableListMultimap<String, Model> map = Multimaps.index(modelList, function);
		return map.asMap();
	}

	/**
	 * 检查目标数量是否为小或负数
	 *
	 */
	private void checkData(List<List<String>> datas, String cateName, List<String> errorList) {
		Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");
		for (int i = 0; i < datas.size(); i++) {
			List<String> columns = datas.get(i);
			for (int j = 1; j < columns.size(); j++) {
				String data = columns.get(j);
				if (StringUtils.isBlank(data) || p.matcher(data).matches()) {
					continue;
				} else {
					String error = String.format(getErrorMsg(MainPushErrorConstants.TASK_ERR_110), cateName, i + 2, j + 1, data);
					errorList.add(error);
				}
			}
		}
	}

	/**
	 * 将所有校验后的数据，一一入库
	 * 
	 * 大区任务表，大区款数关系表，门店任务表
	 *
	 * @param totalList
	 * @param hqTask
	 * @param allModels
	 *            所选月份,当前品类下,对应的所有款数
	 * @param areaList
	 */
	private void saveSecondFileData(List<List<String>> totalList, HqTask hqTask, Collection<Model> allModels, List<MainDiscodeDto> areaList) {
		// 款数数据
		List<String> modelsList = totalList.get(0);
		List<List<String>> datas = totalList.subList(1, totalList.size());
		// 某款数，在某品类下，总目标数 -- 更新款数表的总目标数量的时候需要
		Map<String, Integer> modelTarget = Maps.newHashMap();
		// 大区任务 与 大区款数之间的关系map
		Map<AreaTask, List<AreaModel>> map = Maps.newHashMap();
		// 所有的大区编码set -- 查询门店的时候需要
		Set<String> areaCodeSet = new LinkedHashSet<String>();

		for (int i = 0; i < datas.size(); i++) {
			List<String> columns = datas.get(i);
			String areaName = columns.get(0);
			String areaCode = getAreaCodeByName(areaName, areaList);
			areaCodeSet.add(areaCode);
			// 大区任务
			AreaTask areaTask = new AreaTask(hqTask.getId(), hqTask.getTaskTopic(), areaCode, areaName, hqTask.getCateCode(), hqTask.getCateName(), hqTask.getMonth());
			// 某大区总数量
			List<AreaModel> areaModels = Lists.newArrayList();
			// 每个大区下各款数目标数量
			for (int j = 1; j < columns.size(); j++) {
				String targetNum = columns.get(j);
				String modelName = modelsList.get(j);
				Long modelId = getModelIdByName(modelName, allModels);
				int target = 0;
				// 可能有小数
				if (StringUtils.isNotBlank(targetNum)) {
					double t = Double.parseDouble(targetNum);
					target = (int) Math.round(t);
				}
				AreaModel areaModel = new AreaModel(areaTask.getId(), hqTask.getId(), modelId, target);
				areaModels.add(areaModel);
				// 统计某款数 目标总数
				if (modelTarget.containsKey(modelName)) {
					int tmpTarget = modelTarget.get(modelName) + target;
					modelTarget.put(modelName, tmpTarget);
				} else {
					modelTarget.put(modelName, target);
				}
			}
			map.put(areaTask, areaModels);
		}
		// 当前品类下 所有的大区批量任务入库(目前是48个)
		batchSaveAreaTask(map.keySet());
		// 查询刚入库的areaTask
		List<AreaTask> areaTasks = mainPushTaskHqHandleDao.queryAreaTaskByHqTaskId(hqTask.getId());
		// 库中生成的大区任务id，赋给对应的大区款数关系类
		List<AreaModel> areaModels = dealAreaModels(map, areaTasks);
		// 批量保存所有大区对应的款数关系数据
		batchSaveAreaModel(areaModels);
		// 更新款数表中的总目标数量
		updateModelTargetNum(modelTarget, hqTask.getId());
		// 查询大区对应的门店信息
		List<StoreBaseInfo> storeList = queryStoreByAreaCode(areaCodeSet);
		if (CollectionUtils.isEmpty(storeList)) {
			throw new SawpException("查询门店信息为空");
		}
		// 批量保存门店信息
		saveStoreTask(hqTask, storeList, areaTasks);
		// 更新总部任务状态为已完成
		mainPushTaskHqHandleDao.updateHqTaskStaus(hqTask.getId());
	}

	/**
	 * 根据大区名称获取大区编码
	 */
	private String getAreaCodeByName(String areaName, List<MainDiscodeDto> areaList) {
		for (MainDiscodeDto mainDiscode : areaList) {
			if (mainDiscode.getNote().indexOf(areaName) > -1) {
				return mainDiscode.getDisCodeFive();
			}
		}
		return null;
	}

	/**
	 * 根据款数名称获取款数id
	 * 
	 * @param modelName
	 * @param modelList
	 *            款数名称对应品类下的款数列表
	 * @return
	 */
	private Long getModelIdByName(String modelName, Collection<Model> modelList) {
		for (Model model : modelList) {
			if (modelName.equals(model.getModelName())) {
				return model.getId();
			}
		}
		return null;
	}

	/**
	 * 
	 * 批量保存大区任务
	 *
	 * @param areaTasks
	 */
	@SuppressWarnings("unchecked")
	private void batchSaveAreaTask(Set<AreaTask> areaTasks) {
		Map<String, Object>[] params = new Map[areaTasks.size()];
		int i = 0;
		for (AreaTask areaTask : areaTasks) {
			params[i] = BeanUtil.describe(areaTask);
			i++;
		}
		mainPushTaskHqHandleDao.batchSaveAreaTask(params);
	}

	@SuppressWarnings("unchecked")
	private void batchSaveAreaModel(List<AreaModel> areaModels) {
		Map<String, Object>[] params = new Map[areaModels.size()];
		int i = 0;
		for (AreaModel areaModel : areaModels) {
			params[i] = BeanUtil.describe(areaModel);
			i++;
		}
		mainPushTaskHqHandleDao.batchSaveAreaModelTask(params);
	}

	/**
	 * 根据大区编码和总部任务id，匹配出库中的大区任务，得到自动生成的大区任务id,然后赋给每一大区对应的‘大区款数’类
	 *
	 * @param map
	 * @param areaTasks
	 * @return
	 */
	private List<AreaModel> dealAreaModels(Map<AreaTask, List<AreaModel>> map, List<AreaTask> areaTasks) {
		if (map.size() != areaTasks.size()) {
			throw new SawpException("保存大区款数目标异常，请稍后再试");
		}
		List<AreaModel> list = Lists.newArrayList();
		for (AreaTask areaTask : areaTasks) {
			for (Map.Entry<AreaTask, List<AreaModel>> entry : map.entrySet()) {
				if (areaTask.equals(entry.getKey())) {
					setIdToAreaModel(areaTask.getId(), entry.getValue());
					list.addAll(entry.getValue());
					break;
				}
			}
		}
		return list;
	}

	/**
	 * 向大区款数关联对象 塞入大区任务id
	 *
	 * @param areaTaskId
	 * @param areaModels
	 */
	private void setIdToAreaModel(Long areaTaskId, List<AreaModel> areaModels) {
		for (AreaModel areaModel : areaModels) {
			areaModel.setAreaTaskId(areaTaskId);
		}
	}

	/**
	 * 批量更新款数目标数量
	 *
	 * @param modelTarget
	 * @param hqTaskId
	 */
	@SuppressWarnings("unchecked")
	private void updateModelTargetNum(Map<String, Integer> modelTarget, Long hqTaskId) {
		Map<String, Object>[] params = new Map[modelTarget.size()];
		int i = 0;
		for (Map.Entry<String,Integer> modelData : modelTarget.entrySet()) {
			Map<String, Object> map = Maps.newHashMap();
			map.put("modelName", modelData.getKey());
			map.put("targetNum", modelData.getValue());
			map.put("hqTaskId", hqTaskId);
			params[i] = map;
			i++;
		}
		mainPushTaskHqHandleDao.updateModelTargetNum(params);
	}

	/**
	 * 根据大区编码集合--查询出这些大区下属的所有门店信息
	 *
	 * @param areaCodeSet
	 *            大区编码集合
	 * @return
	 */
	private List<StoreBaseInfo> queryStoreByAreaCode(Set<String> areaCodeSet) {
		List<StoreBaseInfo> allStoreList = Lists.newArrayList();
		for (String disCode : areaCodeSet) {
			List<StoreBaseInfo> storeList = mainPushTaskHqHandleDao.queryStoreByDisCode(disCode);
			allStoreList.addAll(storeList);
		}
		return allStoreList;
	}

	/**
	 * 
	 * 批量保存门店主推任务
	 * 
	 * 常规batch保存3000多条数据需要5s以上
	 *
	 * @param hqTask
	 * @param storeList
	 * @param areaTasks
	 */
	private void saveStoreTask(HqTask hqTask, List<StoreBaseInfo> storeList, List<AreaTask> areaTasks) {
		Map<String, AreaTask> areaTaskMap = Maps.uniqueIndex(areaTasks, new Function<AreaTask, String>() {
			@Override
			public String apply(AreaTask input) {
				return input.getDisCode();
			}
		});
		int i = 0;
		String createTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		StringBuilder sb = new StringBuilder();
		for (StoreBaseInfo store : storeList) {
			String disCode = store.getSaleRegionCode();
			if (disCode == null || areaTaskMap.get(disCode) == null) {
				continue;
			}
			Long areaTaskId = areaTaskMap.get(disCode).getId();
			if (i > 0) {
				sb.append(",");
			}
			sb.append("(").append(hqTask.getId()).append(",").append(areaTaskId).append(",'").append(store.getStoreCode()).append("','").append(store.getStoreName()).append("','")
					.append(hqTask.getCateCode()).append("','").append(hqTask.getCateName()).append("','").append(hqTask.getMonth()).append("','").append(createTime).append("')");
			i++;
		}
		mainPushTaskHqHandleDao.batchSaveStoreTask(sb.toString());
	}
}
